-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:视图引用的自定义函数在having语句后面，部分合理报错
--step1:建表;expect:成功
drop table if exists employees;
CREATE TABLE employees (
employee_id serial PRIMARY KEY,
employee_name varchar(50) NOT NULL,
team_id int,
performance_score numeric(10, 2)
);
INSERT INTO employees (employee_name, team_id, performance_score)
VALUES
('张三', 101, 8000.00),
('李四', 121, 7500.00),
('王五', 132, 8500.00);
--step2:创建自定义函数;expect:成功
drop function if exists calculate_team_performance;
CREATE FUNCTION calculate_team_performance(p_team_id int) RETURNS numeric(10, 2)
AS $$
DECLARE
v_total_score numeric(10, 2);
v_count int;
v_avg_score numeric(10, 2);
BEGIN

SELECT SUM(performance_score), COUNT(*) INTO v_total_score, v_count
FROM employees
WHERE team_id = p_team_id;

IF v_count = 0 THEN
 v_avg_score := 0;
ELSE
 v_avg_score := v_total_score / v_count;
END IF;

RETURN v_avg_score;
END;
$$ LANGUAGE plpgsql;
/
--step3:创建视图;expect:成功
drop view if exists team_performance_view;
CREATE VIEW team_performance_view AS
SELECT team_id, calculate_team_performance(team_id) AS team_performance_score
FROM employees
GROUP BY team_id
HAVING calculate_team_performance(team_id) > 70;
--step4:查询视图;expect:成功
select * from team_performance_view;
--step5:删除自定义函数;expect:成功
drop FUNCTION calculate_team_performance;
--step6:查询视图;expect:报错
select * from team_performance_view;
--step7:查询视图状态;expect:无效
select valid from pg_object where object_oid='team_performance_view'::regclass;
--step8:查询视图定义;expect:有告警
select pg_get_viewdef('team_performance_view');
--step9:重建函数;expect:成功
CREATE FUNCTION calculate_team_performance(p_team_id int) RETURNS numeric(10, 2)
AS $$
DECLARE
v_total_score numeric(10, 2);
v_count int;
v_avg_score numeric(10, 2);
BEGIN

SELECT SUM(performance_score), COUNT(*) INTO v_total_score, v_count
FROM employees
WHERE team_id = p_team_id;

IF v_count = 0 THEN
 v_avg_score := 0;
ELSE
 v_avg_score := v_total_score / v_count;
END IF;

RETURN v_avg_score;
END;
$$ LANGUAGE plpgsql;
/
--step10:查询视图;expect:成功
select * from team_performance_view;
--step11:查询视图状态;expect:无效
select valid from pg_object where object_oid='team_performance_view'::regclass;
--step12:清理环境;expect:成功
drop table employees;
drop view team_performance_view;
drop FUNCTION calculate_team_performance;